﻿/*----------------------------------------------------------------
// Copyright (C) 2017 佰林格都信息科技有限公司 版权所有。 
//
// 文件名：DFamilyInfo
// 文件功能描述：家族信息 FamilyInfo 提供处理各种管理[TB_FamilyInfo]表的数据访问方法
//
// 创建标识：jinchuanliang 2018-07-14
//
// 修改标识：
// 修改描述：
//----------------------------------------------------------------*/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using BLGD.DataAccess;
using BLGD.Entity;
using FamilyTree.Entity;
using System.Text;

namespace FamilyTree.DAL
{
    /// <summary>
    /// 提供处理各种管理家族信息 FamilyInfo  [TB_FamilyInfo]表的数据访问方法
    /// </summary>
	/// <author>jinchuanliang 2018-07-14</author>
    public class DFamilyInfo
    {
        /// <summary>
        /// CRUD实体操作对象
        /// </summary>
        internal readonly static EntityCRUD<FamilyInfoEntity> Crud = new EntityCRUD<FamilyInfoEntity>();

        /// <summary>
        /// 是否已存在此对应数据
        /// </summary>
        /// <param name="strVal"></param> 
        /// <returns></returns>
        public bool IsExists(string strVal)
        {
            string sql = "select FamilyInfoId from TB_FamilyInfo with(nolock) where **=@strVal ";
            List<DbParameter> plist = new List<DbParameter>();
            plist.Add(new SqlParameter("@strVal", strVal));
            object obj = SqlHelper.ExecuteValue(sql, CommandType.Text, plist);

            if (obj != null && Convert.ToInt32(obj) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        /// <summary>
        /// 添加tb_familyInfo数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public int Insert(FamilyInfoEntity model)
        {

            string sql = @"
            INSERT INTO TB_FamilyInfo(FamilyName,Profile,MemberID,CreateTime,IsDelete,ParentId)
            VALUES (@familyname,@profile,@memberid,@createtime,@isdelete,@parentid)
            select @@identity
            ";
            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@familyinfo", model.FamilyInfoId));
            p.Add(new SqlParameter("@familyname", model.FamilyName));
            p.Add(new SqlParameter("@profile", model.Profile));
            p.Add(new SqlParameter("@memberid", model.MemberID));
            p.Add(new SqlParameter("@createtime", model.CreateTime));
            p.Add(new SqlParameter("@isdelete", model.IsDelete));
            p.Add(new SqlParameter("@parentid", model.ParentId));

            object i = SqlHelper.ExecuteValue(sql, CommandType.Text, p);
            return Convert.ToInt32(i);
        } 

        /// <summary>
        /// 更新表信息tb_familyInfo数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool Update(FamilyInfoEntity model)
        {
            string sql = @"
            UPDATE TB_FamilyInfo SET FamilyInfo=@familyinfo,FamilyName=@familyname,Profile=@profile,MemberID=@memberid,CreateTime=@createtime,IsDelete=@isdelete,ParentId=@parentid            
             where FamilyInfoId=@familyInfoId ";
            List<SqlParameter> p = new List<SqlParameter>();
            p.Add(new SqlParameter("@familyInfoId", model.FamilyInfoId));
            p.Add(new SqlParameter("@familyname", model.FamilyName));
            p.Add(new SqlParameter("@profile", model.Profile));
            p.Add(new SqlParameter("@memberid", model.MemberID));
            p.Add(new SqlParameter("@createtime", model.CreateTime));
            p.Add(new SqlParameter("@isdelete", model.IsDelete));
            p.Add(new SqlParameter("@parentid", model.ParentId)); 

            return SqlHelper.ExecuteSql(sql, p) > 0;

        }

        /// <summary>
        /// 更新表信息tb_familyInfo数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool Update(string fileVal, string file, string strWhere)
        {
            string sql = @"
            UPDATE TB_FamilyInfo SET " + file + " = " + fileVal + " where " + strWhere;

            return SqlHelper.ExecuteSql(sql, null) > 0;
        }

        /// <summary>
        /// 标记删除tb_familyInfo数据
        /// </summary>
        /// <param name="familyInfoId">家族ID</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool SignDelete(int familyInfoId)
        {
            string sql = "update TB_FamilyInfo set isdelete=1  where FamilyInfoId=@familyInfoId ";

            List<SqlParameter> plist = new List<SqlParameter>();
            plist.Add(new SqlParameter("@familyInfoId", familyInfoId)); 

            int i = SqlHelper.ExecuteSql(sql, plist);

            return i > 0 ? true : false;
        }


        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="exid"></param>
        /// <returns></returns>
        public FamilyInfoEntity GetModel(int FamilyInfoId)
        {
            const string sql = @"
            SELECT A.FamilyInfo,A.FamilyName,A.Profile,A.MemberID,A.CreateTime,A.IsDelete,A.ParentId 
            FROM TB_FamilyInfo A with(nolock) where FamilyInfoId=@FamilyInfoId and isdelete=0 ";

            List<DbParameter> p = new List<DbParameter>();

            DataTable dt = SqlHelper.ExecuteDt(sql, CommandType.Text, p);
            p.Add(new SqlParameter("@FamilyInfoId", FamilyInfoId));

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<FamilyInfoEntity>(dt)[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="exid"></param>
        /// <returns></returns>
        public List<FamilyInfoEntity> GetModels()
        {
            const string sql = @"
            SELECT A.FamilyInfo,A.FamilyName,A.Profile,A.MemberID,A.CreateTime,A.IsDelete,A.ParentId 
            FROM TB_FamilyInfo A with(nolock) where  isdelete=0  ";

            List<DbParameter> p = new List<DbParameter>();

            DataTable dt = SqlHelper.ExecuteDt(sql, CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<FamilyInfoEntity>(dt);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<FamilyInfoEntity> GetModels(int pageIndex, int pageSize)
        {
            StringBuilder sb = new StringBuilder(@"
            SELECT top (@pageSize) A.FamilyInfo,A.FamilyName,A.Profile,A.MemberID,A.CreateTime,A.IsDelete,A.ParentId 
            FROM TB_FamilyInfo A with(nolock) where  isdelete=0  ");
            if (pageIndex > 1)
            {
                sb.Append(@" and A.FamilyInfoId <(select min(FamilyInfoId) as mid from (select top ((@pageIndex-1)*@pageSize) FamilyInfoId from TB_FamilyInfo  where  isdelete=0 order by FamilyInfoId desc) t)");
            }
            sb.Append(" order by A.FamilyInfoId desc ");

            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@pageIndex", pageIndex));
            p.Add(new SqlParameter("@pageSize", pageSize));

            DataTable dt = SqlHelper.ExecuteDt(sb.ToString(), CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<FamilyInfoEntity>(dt);
            }
            else
            {
                return null;
            }
        }
    }
}

